package com.common.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelUtils {

	/**
	 * @param filename
	 *            文件名
	 * @param sheetIndex
	 *            表索引
	 * 
	 * @return List<Map<String, String>> [表头:值]的映射列表
	 */
	public static List<Map<String, String>> readExcel(String filename,
			int sheetIndex) {
		Workbook wb = null;
		List<Map<String, String>> list = null;
		try {
			File file = new File(filename);
			wb = Workbook.getWorkbook(file);
			Sheet sheet = wb.getSheet(sheetIndex);
			// int columnLen = sheet.getColumns();
			int rowLen = sheet.getRows();

			list = new ArrayList<Map<String, String>>();

			List<String> headerList = getHeader(wb, sheetIndex, 0);

			// i:表头索引
			for (int i = 2; i < rowLen; i++) {
				Map<String, String> map = new HashMap<String, String>();
				Cell[] cell = sheet.getRow(i);
				for (int j = 0; j < cell.length; j++) {
					String rowVal = cell[j].getContents();
					map.put(headerList.get(j), rowVal);
				}
				list.add(map);
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.close();
			}
		}

		return list;
	}

	/**
	 * @param filename
	 *			文件名
	 * @param sheetIndex
	 *			表索引
	 * @param headerIndex
	 * 			表头的索引
	 * @return List<String> 
	 * 			表头
	 */
	public static List<String> getHeader(String filename, int sheetIndex,
			int headerIndex) {
		List<String> list = new ArrayList<String>();
		Workbook wb = null;
		try {
			File file = new File(filename);
			wb = Workbook.getWorkbook(file);

			list = getHeader(wb, sheetIndex, headerIndex);

		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.close();
			}
		}

		return list;
	}

	private static List<String> getHeader(Workbook wb, int sheetIndex,
			int headerIndex) {
		List<String> list = new ArrayList<String>();
		Sheet sheet = wb.getSheet(sheetIndex);

		Cell[] cell = sheet.getRow(headerIndex);
		int cellLen = cell.length;
		for (int j = 0; j < cellLen; j++) {
			String cellValue = cell[j].getContents();
			list.add(cellValue);
		}
		return list;
	}

	public static void main(String[] args) {
		String filename = "d:/book.xls";
		// print(readExcel(filename, 0));
		// print2(getHeader(filename, 0, 0));
	}

	private static void print(List<Map<String, String>> list) {
		for (Map<String, String> _map : list) {
			for (Map.Entry<String, String> pair : _map.entrySet()) {
				System.out.println(pair.getKey() + "\t" + pair.getValue());
			}
		}
	}

	/*
	 * 
	 * 
	 * private static void print2(List<String> list) { for (String str : list) { System.out.println(str); } }
	 */
}
